package com.zhenwgang.listen_books.repository;

import com.zhenwgang.listen_books.data_object.TidToCountDO;
import com.zhenwgang.listen_books.util.DBUtil;
import com.zhenwgang.listen_books.util.Log;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author Violet
 * @date 2022/07/11 15:32
 **/
public class RelationRepo {
    @SneakyThrows
    public void insertByAidAndTid(int aid, List<Integer> tidList){
        String sqlFormat = "insert into relations (aid, tid) values %s";
        String s = tidList.stream().map(tid -> String.format("(%d, %d)", aid, tid)).collect(Collectors.joining(", "));
        String sql = String.format(sqlFormat, s);
        Log.println("执行 SQL: " + sql);

        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.executeUpdate();
            }
        }
    }

    @SneakyThrows
    public List<Integer> selectListByAid(int aid){
        List<Integer> list = new ArrayList<>();
        String sql = "select tid from relations where aid = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, aid);

                Log.println("执行的SQL为：" + ps);

                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        list.add(rs.getInt(1));
                    }
                }
            }
        }
        Log.println("list为：" + list);
        return list;
    }

    @SneakyThrows
    public List<TidToCountDO> selectCountByTidListGroupByTidList(List<Integer> tidList){
        String sqlFormat = "select tid, count(*) as ref_count from relations where tid in (%s) group by tid order by tid";
        StringBuilder sb = new StringBuilder();
        for (Integer tid : tidList) {
            sb.append(tid).append(", ");
        }
        sb.delete(sb.length() - 2, sb.length());
        String sql = String.format(sqlFormat, sb.toString());

        List<TidToCountDO> list = new ArrayList<>();
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                Log.println("执行SQL: " + ps);
                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        TidToCountDO tidToCountDO = new TidToCountDO(rs.getInt("tid"), rs.getInt("ref_count"));
                        list.add(tidToCountDO);
                    }
                }
            }
        }
        return list;
    }
}